package cn.edu.scau.cmi.ema.service.base;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.edu.scau.cmi.ema.domain.Adultertype;
import cn.edu.scau.cmi.ema.domain.Foodtype;
import cn.edu.scau.cmi.ema.util.CmiSetUtil;
import cn.edu.scau.cmi.front.domain.FrontEventPagination;

/*FrontEventSqlUtil是CmiSqlUtil的具体类，因为有一些数据需要注入，因此，使用service bean的方式，而不是采用静态类的方法。
名字也修改成为FrontEventSqlService*/
@Service
public class FrontEventSqlService extends CmiEmaService{
	
//	???冗余的方法，待清理
	@Transactional
	public String createSelectHql(FrontEventPagination page, Class<?> entity) {
		String entityName = entity.getName();
		StringBuilder hqlStringBuilder = new StringBuilder();
		hqlStringBuilder.append("select myEntity from " + entityName + " myEntity");
		
		appendBasicFieldCriteria(page, entity, hqlStringBuilder);
//		界面中点击的几个主要的类型，这些类型包含所有的子类型而不仅仅是等于该类型
		appendRootFoodtypeCriteria(page, hqlStringBuilder);
		appendAdultertype(page, hqlStringBuilder);
		appendTimeQuantum(page, hqlStringBuilder);

		System.out.println("拼接的查询语句：FrontEventSqlService appCondtion createSelectHql:" + hqlStringBuilder.toString());

		return hqlStringBuilder.toString();
	}


	private void appendTimeQuantum(FrontEventPagination page, StringBuilder hqlStringBuilder) {
	//		(2.3) 时间范围timeQuantum
			int timeQuantum = page.getTimeQuantum();
			if(timeQuantum > 0) {
	//			HQL中时间的比较要采用的方式：myEntity.discoveryTime <'2020-01-01 00:00:00'";
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				Calendar today = Calendar.getInstance();
				today.add(Calendar.MONTH, -timeQuantum);
				simpleDateFormat.format(today.getTime());
	//			如果已经有了条件，需要在后面添加条件的时候使用连接词and
				if(hqlStringBuilder.indexOf("where") == -1) {
					hqlStringBuilder.append(" where ");
				}else {
					hqlStringBuilder.append(" and ");
				}
				hqlStringBuilder.append(" myEntity.discoveryTime > '" + simpleDateFormat.format(today.getTime()) + "'");
			}
	}


	private void appendAdultertype(FrontEventPagination page, StringBuilder hqlStringBuilder) {
		Adultertype selectAdultertype = page.getSelectAdultertype();
		if(selectAdultertype == null || "".equals(selectAdultertype.getName())) {
			return;
		}
//		将页面中传递过来的掺假类型转换成为数据库中的实体类型
		selectAdultertype = CmiSetUtil.getFirstInstance(adultertypeDAO.findAdultertypeByName(selectAdultertype.getName()));
		
		if(selectAdultertype != null && selectAdultertype.getId() != -2) {
	//			如果已经有了条件，需要在后面添加条件的时候使用连接词and
			if(hqlStringBuilder.indexOf("where") == -1) {
				hqlStringBuilder.append(" where ");
			}else {
				hqlStringBuilder.append(" and ");
			}
			hqlStringBuilder.append(" exists (select adultertype from myEntity.adultertypesForEvent adultertype where adultertype.id = '" + selectAdultertype.getId()  +"')");//
		}
	}
	
	
	@Transactional
//	这个方法用于统计符合页面传递过来的请求条件数量，和下面的方法同样的作用。
	public String createCountHql(FrontEventPagination page, Class<?> entityClass) {
		String entityName = entityClass.getName();
		StringBuilder hqlStringBuilder = new StringBuilder();
		hqlStringBuilder.append("select count(myEntity) from " + entityName + " myEntity");
		
		appendBasicFieldCriteria(page, entityClass, hqlStringBuilder);
		
		appendRootFoodtypeCriteria(page, hqlStringBuilder);
		
		if(page.getTimeQuantum() != -1) {
			hqlStringBuilder.append("");
		}
		
		if(!"".equals(page.getSearchEventName())){
			
		}
		
		if(page.getIsTimeDescending()) {
			
		}
		
//		if (hqlStringBuilder.length() > 0) {
//			hqlStringBuilder.insert(0, " where ");
//		}
		
		return hqlStringBuilder.toString();
	}

//	TODO 下面的方法是否有用，待检查
//	与CmiDetachedCriteriaUtil类的createDetachedCriteria方法类似，需要仔细检查一下是否有缺陷
	public DetachedCriteria createDetachedCriteria(FrontEventPagination cmiPagination, Class<?> entity) {
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(entity, "myEvent");
		
		Map<String, String> compositeConditionMap = cmiPagination.getBasicPropertyCriteriaMap();
		Set<Entry<String, String>> compositeContitionMapEntrySet = compositeConditionMap.entrySet();

		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date());
		
		//添加时间段条件
		if(cmiPagination.getTimeQuantum()!=0) {
			calendar.add(Calendar.DATE, -cmiPagination.getTimeQuantum()*31);
			detachedCriteria.add(Restrictions.between("discoveryTime", calendar, Calendar.getInstance()));
		}
		//添加排序条件
		
		System.out.println("cmiPagination.getIsTimeAscendingOrder()" + cmiPagination.getIsTimeDescending());
		
		if(cmiPagination.getIsTimeDescending()) {//降序
			detachedCriteria.addOrder(Order.asc("discoveryTime"));
		}else {
			detachedCriteria.addOrder(Order.desc("discoveryTime"));
		}
		
		for(Entry<String, String> enrty: compositeContitionMapEntrySet) {
			String conditionField = enrty.getKey();
			conditionField = conditionField.substring(0, 1).toLowerCase() + conditionField.substring(1);
			String conditionValue = enrty.getValue();
			if(conditionValue == null) {
				continue;
			}
			if("".equals(conditionValue.trim())) {
				continue;
			}
//			TODO　2021-05-26，如果是String类型，使用模糊查巡，如果是其他类型，就使用精确查询。
			try {
				if (entity.getDeclaredField(conditionField).getType().getName().equals("java.lang.String")) {
					detachedCriteria.add(Restrictions.like(conditionField, conditionValue.trim()));
//					detachedCriteria.add(Restrictions.sqlRestriction("myEvent." + selectCompositeCondition + " like \'%" + inputCompositeCondition + "%\'"));
				} else {
					detachedCriteria.add(Restrictions.eq(conditionField, conditionValue.trim()));
//					detachedCriteria.add(Restrictions.sqlRestriction("myEvent." + selectCompositeCondition + " = \'" + inputCompositeCondition + "\'"));
				}
			} catch (NoSuchFieldException | SecurityException e) {
				e.printStackTrace();
			}
		}
		return detachedCriteria;
	}

	private void appendRootFoodtypeCriteria(FrontEventPagination page, StringBuilder hqlStringBuilder) {
		Foodtype selectFoodtype = page.getSelectFoodtype();
//		注意了：即使没有输入foodtype, 但是因为页面有一个form:hidden的selectFoodtype，这样，在list页面中提交的时候
//		会自动封装foodtype和adultertype对象，只是这个对象的id, name等都是null在新建page对象的时候，默认是创建了一个对象，只是属性的值都是null
//		食物类别：有一个总的类别，这个总的类别下面有多个子类别，都应该添加上去。
		if(selectFoodtype!=null && selectFoodtype.getId() !=null) {
			Integer selectRootFoodtypeId = selectFoodtype.getId();
			Set<Integer> selectFoodtypeIds = new LinkedHashSet<Integer> ();
			switch (selectRootFoodtypeId) {
			case 561:
				selectFoodtypeIds = processedIdSet;
				break;
			case 562:
				selectFoodtypeIds = grainAndOilIdSet;
				break;
			case 563:
				selectFoodtypeIds = oilIdSet;
				break;
			case 565:
				selectFoodtypeIds = meatIdSet;
				break;
			case 566:
				selectFoodtypeIds = milkIdSet;
				break;
			case 583:
				selectFoodtypeIds = aquaticIdSet;
				break;
			case 592:
				selectFoodtypeIds = otherIdSet;
				break;
			case 1642:
				selectFoodtypeIds = agriculturalProductIdSet;
				break;
			}
			
			StringBuilder idStringBuilder = new StringBuilder(); 
			for(Integer id : selectFoodtypeIds) {
				idStringBuilder.append(id);
				idStringBuilder.append(", "); 
			}
			
			StringBuilder foodtypeCriteriaStringBuilder = new StringBuilder();
			
//			如果已经有了条件，需要在后面添加条件的时候使用连接词and
			
			if(idStringBuilder.length() > 0) {
				if(hqlStringBuilder.indexOf("where") == -1) { 
					hqlStringBuilder.append(" where "); 
				}else {
					hqlStringBuilder.append(" and "); 
				}
				foodtypeCriteriaStringBuilder.append(" myEntity.foodbatch.food.foodtype.id in ("  + idStringBuilder.substring(0, idStringBuilder.length()-2) + ")");
				hqlStringBuilder.append(foodtypeCriteriaStringBuilder);//
			}
			
			System.out.println("食物类型的查询条件是：" + hqlStringBuilder.toString());
		}
	}



	private void appendBasicFieldCriteria(FrontEventPagination page, Class<?> entity, StringBuilder hqlStringBuilder) {
		Map<String, String> basicFieldCirteriaMap = page.getBasicPropertyCriteriaMap();
		Set<Entry<String, String>> baseFieldPropertyCriteriaMapEntrySetEntrySet  = basicFieldCirteriaMap.entrySet();
//		基本条件部分
		StringBuilder basicFieldCriteriaStringBuilder = new StringBuilder();
		for(Entry<String, String> enrty: baseFieldPropertyCriteriaMapEntrySetEntrySet) {
			String basicFieldName = enrty.getKey();
			basicFieldName = basicFieldName.substring(0, 1).toLowerCase() + basicFieldName.substring(1);
			String basicFieldValue = enrty.getValue();
			if("".equals(basicFieldValue.trim())) {
				continue;
			}
//			如果原来的条件还没有，就不需要添加and，如果已经有了条件，新建的条件应该在前面添加and连接词
			if(basicFieldCriteriaStringBuilder.length() > 0) {
				basicFieldCriteriaStringBuilder.append(" and ");
			}
			
			basicFieldCriteriaStringBuilder.append(" myEntity." + basicFieldName);
			
			try {
				if (entity.getDeclaredField(basicFieldName).getType().getName().equals("java.lang.String")) {
					basicFieldCriteriaStringBuilder.append(" like \'%" + basicFieldValue.trim() + "%\'");
				} else {
					basicFieldCriteriaStringBuilder.append(" = " + "\'" + basicFieldValue.trim() + "\'");
				}
			} catch (NoSuchFieldException e) {
				e.printStackTrace();
			} catch (SecurityException e) {
				e.printStackTrace();
			}
		}
		
		if(basicFieldCriteriaStringBuilder.length()>0) {
			hqlStringBuilder.append(" where ").append(basicFieldCriteriaStringBuilder);
		}
	}
}